﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.SECURITY.MODEL;

namespace KPIS.GERP.SECURITY.DAL
{
    public class UserRoleDAL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable LoadUserRole(Nullable<int> Sequence, Nullable<int> UserSequence)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@UserSequence", UserSequence);
                dbManager.AddParameters(2, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                strSQL = "SELECT a.*"
                    + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS USER_NAME"
                    + ", r.ROLE_NAME"
                    //+ ", u.user_id, u.user_name, r.role_name, r.remarks"
                    + " FROM SYS_SC_USER_ROLES a"
                    + " LEFT JOIN SYS_SC_ROLES r ON a.ROLE_SEQ = r.ROLE_SEQ"
                    + " LEFT JOIN SYS_SC_USERS u ON a.USER_SEQ = u.USER_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e ON u.EMP_SEQ = e.EMP_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " WHERE a.RECORD_STATUS = @RecordStatus"
                    + (Sequence.HasValue ? " AND a.USER_ROLE_SEQ = @Sequence" : "")
                    + (UserSequence.HasValue ? " AND u.USER_SEQ = @UserSequence" : "");

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }
        public DataTable LoadUserRoleUnion(Nullable<int> Sequence, Nullable<int> UserSequence, string SystemID)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@UserSequence", UserSequence);
                dbManager.AddParameters(2, "@SystemID", SystemID);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                strSQL = "SELECT t.* "
                    + " FROM("
                        + " SELECT a.USER_ROLE_SEQ, a.USER_SEQ, a.ROLE_SEQ"
                        + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS USER_NAME"
                        + ", r.ROLE_NAME, r.SYSTEM AS SYSTEM_ID"
                        + ", 1 AS HAS_ROLE"
                        + " FROM SYS_SC_USER_ROLES a"
                        + " LEFT JOIN SYS_SC_ROLES r ON a.ROLE_SEQ = r.ROLE_SEQ"
                        + " LEFT JOIN SYS_SC_USERS u ON a.USER_SEQ = u.USER_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEES e ON u.EMP_SEQ = e.EMP_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " WHERE a.RECORD_STATUS = @RecordStatus"
                        + (Sequence.HasValue ? " AND a.USER_ROLE_SEQ = @Sequence" : "")
                        + (UserSequence.HasValue ? " AND u.USER_SEQ = @UserSequence" : "")
                        + (SystemID.Length > 0 ? " AND r.SYSTEM = @SystemID" : "")
                        + " UNION ALL"
                        + " SELECT NULL AS USER_ROLE_SEQ"
                            + ", NULL AS USER_SEQ"
                            + ", rr.ROLE_SEQ"
                            + ", NULL AS USER_NAME, rr.ROLE_NAME, rr.SYSTEM AS SYSTEM_ID"
                            + ", 0 AS HAS_ROLE"
                        + " FROM SYS_SC_ROLES rr"
                        + " WHERE rr.RECORD_STATUS = @RecordStatus"
                        + (SystemID.Length > 0 ? " AND rr.SYSTEM = @SystemID" : "")
                        + " AND rr.ROLE_SEQ NOT IN("
                                + " select role_seq from sys_sc_user_roles"
                                + " where system = @SystemID "
                                + " and record_status = @RecordStatus"
                                + " and user_seq = @UserSequence )"
                    + ")t";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        } 
        public void Insert(int UserSequence, int RoleSequence, int CreatedBy)
        {
            try
            {
                string strSQL;

                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@UserSequence", UserSequence);
                dbManager.AddParameters(1, "@RoleSequence", RoleSequence);
                dbManager.AddParameters(2, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(3, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(4, "@CreatedWhen", DateTime.Now);

                strSQL = "INSERT INTO SYS_SC_USER_ROLES"
                    + "(USER_SEQ, ROLE_SEQ, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@UserSequence, @RoleSequence, @RecordStatus, @CreatedBy, @CreatedWhen)";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(int Sequence, int UserSequence, int RoleSequence, int UpdatedBy)
        {
            try
            {
                string strSQL;

                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@UserSequence", UserSequence);
                dbManager.AddParameters(2, "@RoleSequence", RoleSequence);
                dbManager.AddParameters(3, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(4, "@UpdatedWhen", DateTime.Now);

                strSQL = "UPDATE SYS_SC_USER_ROLES"
                    + " SET USER_SEQ = @UserSequence"
                        + ", ROLE_SEQ = @RoleSequence"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE USER_ROLE_SEQ = @Sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            try
            {
                string strSQL;

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Now);

                strSQL = "UPDATE SYS_SC_USER_ROLES"
                    + " SET RECORD_STATUS = @DeleteRecord"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE USER_ROLE_SEQ = @Sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
